from django.core.paginator import Paginator
from django.shortcuts import render_to_response, render,Http404
from django.http import JsonResponse,QueryDict,HttpResponse,StreamingHttpResponse
from django.core.files.storage import default_storage
from django.core.files.base import ContentFile
from django.db import  connection,transaction
from django.db.models import Q
import json
import uuid
import ftplib
# Create your views here.
from muluguanli.models import Mulu
from TDSD.settings import STORE_DIR_FTP
import csv
from yonghuguanli.models import TdsdUser,TdsdApplyResource,TdsdMessage
from ziyuanguanli.models import TdsdResourceList,TdsdTableDesc,TdsdFtpResourceList
from shujuyuan.models import TdsdDataSource,TdsdFtpSource
from muluguanli.models import Mulu
import time
import pymysql.cursors
import cx_Oracle
from  django.views.decorators.csrf import csrf_exempt
import traceback
from django.utils.encoding import escape_uri_path
from utils import util
#oracle编码问题
import os
os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8'

@util.user_login
def index(request,resourcetype):
    '''
    主页
    :param request:
    :return:
    '''
    userid=request.session.get('user_id')
    if userid:
        datasourcelist=TdsdDataSource.objects.all().filter(user_id=userid).filter(isdelete=0)
    if resourcetype==1:
        return render_to_response('resourcemanage.html',locals())
    elif resourcetype==2:
        ftphostlist=TdsdFtpSource.objects.filter(isdelete=0).filter(user_id=userid)
        return render_to_response('resourceFtpmanage.html',locals())
    else:
        raise Http404


@csrf_exempt
def resourcelist(request):
    '''
    资源创建管理视图
    :param request:
    :return:
    '''
    data={}
    requestmethod=request.method
    userid=request.session.get('user_id')
    print("userid=",userid)
    if requestmethod=="GET":
        param=request.GET
        page=int(param.get('page',1))
        limit=int(param.get('limit',10))
        search_kw=param.get('key',"")
        resource_cate=param.get('did','')#目录id=分类id
        db_id=param.get('dbid','')#数据库id=数据源id
        _resource_status=int(param.get('resource_status',0))
        if userid==1:
            base_sql="select a.resource_id,a.resource_name,a.resource_desc,a.resource_status,a.addtimes,a.updatetime,a.tablename,b.source_name,c.name \
                      from tdsd_resource_list as a inner join tdsd_data_source as b  inner join tdsd_mulu as c  \
                      where a.db_id=b.id and a.resource_cate=c.id and a.status=0 "
        else:
            base_sql="select a.resource_id,a.resource_name,a.resource_desc,a.resource_status,a.addtimes,a.updatetime,a.tablename,b.source_name,c.name \
                      from tdsd_resource_list as a inner join tdsd_data_source as b  inner join tdsd_mulu as c  \
                      where a.db_id=b.id and a.resource_cate=c.id and userid={userid} and a.status=0".format(userid=userid)
        if search_kw:
            base_sql +=" and resource_name like '%{resource_name}%'".format(resource_name=search_kw.strip())
        if _resource_status:
            if _resource_status==1:#公开
                resource_status=1
            elif _resource_status==2:#授权
                resource_status=0
            base_sql+=" and resource_status={resource_status}".format(resource_status=resource_status)
        if resource_cate:
            base_sql += " and resource_cate={resource_cate}".format(resource_cate=resource_cate)
        if db_id:
            base_sql += " and db_id={db_id}".format(db_id=db_id)
        print(base_sql)
        cursor=connection.cursor()
        cursor.execute(base_sql)
        result=cursor.fetchall()
        localresult=result[(page - 1) * limit:limit + (page - 1) * limit]
        # print("ddd=",ddd)
        # result=TdsdResourceList.objects.raw(base_sql)[(page - 1) * limit:limit + (page - 1) * limit]
        for i in result:
            data.setdefault('data',[]).append({
                "id":i[0],
                "name":i[6],
                "rtype":i[-1],
                "db_name":i[-2],
                "resource_name":i[1],
                "r_status":"授权" if i[3]==0 else "公开" ,
                "addtimes":time.strftime("%Y-%m-%d %H:%M:%S",time.localtime(i[4])),
                "updatetime":time.strftime("%Y-%m-%d %H:%M:%S",time.localtime(i[5])),
            })
        data['count']=len(result)
        data['code']=0
    elif requestmethod=="POST":
        #添加资源
        data={}
        params=json.loads(request.body.decode('utf-8'))
        addtimes=int(time.time())
        try:
            resource_name=params.get("resource_name",'')
            resource_dir=params.get("resource_dir")
            insertResourceId=params.get("insertResourceId")
            assert resource_name and resource_dir,u'资源名称和目录必选'
            if insertResourceId:
                TdsdResourceList.objects.filter(resource_id=insertResourceId).update(
                    resource_name=params.get("resource_name"),
                    resource_cate=params.get("resource_dir"),
                    resource_desc=params.get("resource_desc"),
                    resource_content=params.get("resource_content"),
                    resource_info=params.get("resource_info"),
                    updatetime=int(time.time()),
                    resource_status=params.get('resource_status'),
                    expiretime=params.get("expiretime"),
                    blackbook=params.get("blackbook")
                )
            else:
                resourcedata=TdsdResourceList.objects.create(
                    resource_name=params.get("resource_name"),
                    resource_cate=params.get("resource_dir"),
                    resource_desc=params.get("resource_desc"),
                    resource_content=params.get("resource_content"),
                    resource_info=params.get("resource_info"),
                    db_id=params.get("db_id"),
                    userid=request.session.get('user_id'),
                    addtimes=addtimes,
                    updatetime=addtimes,
                    resource_status=params.get('resource_status'),
                    tablename=params.get('tablename'),
                    expiretime=params.get("expiretime"),
                    blackbook=params.get("blackbook")
                )
                resourcedata.save()
            data['code']=0
            data['msg']=''
        except Exception as e:
            traceback.print_exc()
            data['code']=1
            data['msg']=str(e)

        print("params=",params)
        return JsonResponse(data)
    elif requestmethod=="DELETE":
        params=QueryDict(request.body.decode("utf-8"),encoding="utf-8")
        rid=params.get("resource_id")
        TdsdResourceList.objects.filter(resource_id=rid).update(status=1)
        data['code']=0
        data['msg']=""
        return JsonResponse(data)
    elif requestmethod=="PUT":
        pass
        # params = QueryDict(request.body)
        # print(paramss)
        # resource_id=params.get('resourceid')
        # TdsdResourceList.objects.filter(resource_id=resource_id).update(
        #     resource_name=params.get("resource_name"),
        #     resource_cate=params.get("resource_dir"),
        #     resource_desc=params.get("resource_desc"),
        #     resource_content=params.get("resource_content"),
        #     resource_info=params.get("resource_info"),
        #     updatetime=int(time.time()),
        #     resource_status=params.get('resource_status'),
        # )
        # data['code']=0
        # data['msg']=""
    return JsonResponse(data)


@csrf_exempt
def ftpresourcelist(request):
    '''
    ftp资源的增删改查
    :param request:
    :return:
    '''
    data={}
    method=request.method
    userid=request.session.get("user_id")
    if method=="POST":
        params=request.POST
        print(params)
        updateflag=params.get("fid","")
        ftpname=params.get("ftpname")
        filename=params.get("filename","filename")
        ftpaddress=params.get("ftpaddress")
        ftpdesc=params.get("ftpdesc")
        fileurl=params.get("fileurl")
        ftpid=params.get("fid")
        resource_status=params.get("resource_status")
        try:
            assert userid,"用户未登录"
            assert ftpname,"ftp资源名称不能为空"
            assert filename,"ftp资源必选"
            assert ftpaddress,"未选择ftp服务器"
            if updateflag:
                print("update")
                TdsdFtpResourceList.objects.filter(id=ftpid).update(
                    fname=ftpname,
                    updatetimes=int(time.time()),
                    desc=ftpdesc,
                    resource_status=resource_status,
                )
            else:
                print("insert")
                ftpresource=TdsdFtpResourceList.objects.create(
                    fid=''.join(str(uuid.uuid1()).split('-')),
                    fname=ftpname,
                    addtimes=int(time.time()),
                    updatetimes=int(time.time()),
                    uid=userid,
                    ftpid=ftpaddress,
                    desc=ftpdesc,
                    resource_cate=params.get("selectdir"),
                    fileurl=fileurl,
                    resource_status=resource_status,
                    filename=fileurl.split('/')[-1]

                )
                ftpresource.save()
            data['code']=0
            data['msg']=""
        except Exception as e:
            data['code']=1
            data['msg']=str(e)
    if method=="GET":
        params=request.GET
        page=int(params.get('page',1))
        limit=int(params.get('limit',10))
        search_kw = params.get('key', "")
        basesql="select A.`id`,A.`addtimes`,A.`filename`,A.`desc`,A.`fid`,A.`fname`,B.`username`,C.`ftp_name`,D.`name` as dname from tdsd_ftp_resource_list as A " \
                "inner join tdsd_user as B inner join `tdsd_ftp_source` as C inner join `tdsd_mulu` as D on A.`ftpid`=C.`ftp_id`" \
                " and A.`uid`=B.`id` and A.`resource_cate`=D.`id` and A.`isdelete`=0 and A.`uid`={uid}".format(uid=userid)
        print("basesql=",basesql)
        if search_kw:
            basesql +=" and A.`fname` like '%{fname}%'".format(fname=search_kw.strip())
        resourcelist=TdsdFtpResourceList.objects.raw(basesql)
        count=0
        for i in resourcelist:
            print(i)
            if count>=(page - 1) * limit:
                data.setdefault('data',[]).append({
                    "id":i.id,
                    "resource_name":i.fname,
                    "ftp_name":i.ftp_name,
                    "rtype":i.dname,
                    "filename":i.filename,
                    "r_status":"授权" if i.resource_status==0 else "公开",
                    "addtimes":time.strftime("%Y-%m-%d %H:%M:%S",time.localtime(i.addtimes)),
                    "updatetime":time.strftime("%Y-%m-%d %H:%M:%S",time.localtime(i.updatetimes)),
                })
            elif count==limit + (page - 1) * limit:
                break
        data['count']=0
        data['code']=0
        print(data)
    if method=="DELETE":
        params=QueryDict(request.body.decode("utf-8"),encoding="utf-8")
        _id=params.get("resource_id")
        if _id:
            TdsdFtpResourceList.objects.filter(id=_id).update(
                isdelete=1
            )
            data['code'] = 0
            data['msg'] = ""
        else:
            data['code']=1
            data['msg']="id不能为空"
    return JsonResponse(data)

@csrf_exempt
def updatetable(request):
    '''
    更新表的描述信息
    :param request:
    :return:
    '''
    data={}
    params=json.loads(request.body)
    localtime=int(time.time())
    try:
        db_id=params.get('dbid')
        tablename=params.get('tablename')
        resourceid=params.get("resourceid")
        # checkvalue=TdsdTableDesc.objects.filter(db_id=db_id,tablename=tablename)
        if resourceid:
            TdsdResourceList.objects.filter(resource_id=resourceid).update(
                resource_info=params.get('columnsdesc'),
                updatetime=localtime
            )
        if db_id and tablename:
            print('xxx')
            TdsdTableDesc.objects.filter(db_id=db_id).filter(tablename=tablename).update(
                columnsdesc=params.get('columnsdesc'),
                updatetime=localtime
            )

        else:
            print('111')
            tabledesc=TdsdTableDesc.objects.create(
                tablename=params.get('tablename'),
                columnsdesc=params.get('columnsdesc'),
                addtimes=localtime,
                updatetime=localtime,
                db_id=params.get('dbid')
            )
            tabledesc.save()
        data['code']=0
        data['msg']=""
    except Exception as e:
        import traceback
        traceback.print_exc()
        data['code']=1
        data['msg']=str(e)
    return JsonResponse(data)

def gettableslist(request,datasourceId):
    '''
    获取数据库表和表结构信息
    :param request:
    :return:
    '''
    data={}
    page=int(request.GET.get('page',1))
    limit=int(request.GET.get('limit',7))
    datasourceId=int(datasourceId)
    result=TdsdDataSource.objects.filter(id=datasourceId).first()
    if result.db_cate=="MySQL":
        conn_url_dict={
            "host":result.host_ip,
            "user":result.username,
            "password":result.password,
            "db":result.sid,
            "charset":"utf8",
            "cursorclass":pymysql.cursors.DictCursor

        }
        print(conn_url_dict)
        try:
            conn=pymysql.connect(**conn_url_dict)
            with conn.cursor() as cursor:
                cursor.execute("SHOW TABLE STATUS")
                tableresult=cursor.fetchall()
                datadict=[]
                for i in tableresult:
                    datadict.append({
                        "tableName":i['Name'],
                        "tableComment":i['Comment'],
                        "operate":'<button class="layui-btn layui-btn-xs" ><i class="layui-icon layui-icon-add-circle"></i></button>'
                    })
                data['data']=datadict[(page - 1) * limit:limit + (page - 1) * limit]
                data['code']=0
                data['count']=len(tableresult)
        except Exception as e:
            print(str(e))
            data['code']=1
            data['msg']=str(e)
        finally:
            conn.close()

    elif result.db_cate=="Oracle":
        oracleUrl='{user}/{passwd}@{ip}/{db}'.format(user=result.username,passwd=result.password,ip=result.host_ip,db=result.sid)
        conn = cx_Oracle.connect(oracleUrl)
        with conn.cursor() as cursor:
            cursor.execute("select t.table_name,f.comments from all_tables t inner join user_tab_comments f on t.table_name = f.table_name where owner='{db}'".format(db=result.sid))
            tableresult = cursor.fetchall()
            datadict = []
            for i in tableresult:
                print(i)
                datadict.append({
                    "tableName": i[0],
                    "tableComment": i[1],
                    "operate": '<button class="layui-btn layui-btn-xs" ><i class="layui-icon layui-icon-add-circle"></i></button>'
                })
            data['data'] = datadict[(page - 1) * limit:limit + (page - 1) * limit]
            data['code'] = 0
            data['count'] = len(tableresult)
    return  JsonResponse(data)

def gettablesinfo(request,tablename,db_id):
    '''
    获取某个数据库的表字段信息
    :param request:
    :param tablename:
    :param db_id:
    :return:
    '''
    data={}
    page=int(request.GET.get('page',1))
    limit=int(request.GET.get('limit',7))
    result = TdsdDataSource.objects.filter(id=int(db_id)).first()
    if result.db_cate == "MySQL":
        conn_url_dict = {
            "host": result.host_ip,
            "user": result.username,
            "password": result.password,
            "db": result.sid,
            "charset": "utf8",
            "cursorclass": pymysql.cursors.DictCursor

        }
        try:
            conn = pymysql.connect(**conn_url_dict)
            with conn.cursor() as cursor:
                cursor.execute("SHOW FULL COLUMNS FROM {}".format(tablename))
                tableresult = cursor.fetchall()
                datadict = []
                for i in tableresult:
                    datadict.append({
                        "columns": i['Field'],
                        "columntype": i['Type'],
                        "desc": i['Comment'],
                        "editdesc": i['Comment'],
                    })
                data['data']=datadict[(page - 1) * limit:limit + (page - 1) * limit]
                data['code']=0
                data['count']=len(tableresult)
        except Exception as e:
            data['code'] = 1
            data['msg'] = str(e)
        finally:
            conn.close()
    elif result.db_cate=="Oracle":
        oracleUrl='{user}/{passwd}@{ip}/{db}'.format(user=result.username,passwd=result.password,ip=result.host_ip,db=result.sid)
        conn = cx_Oracle.connect(oracleUrl)
        with conn.cursor() as cursor:
            cursor.execute("SELECT b.COLUMN_NAME,b.DATA_TYPE,a.COMMENTS \
                          FROM   USER_TAB_COLUMNS b,USER_COL_COMMENTS a \
                          WHERE \
                              b.TABLE_NAME = '{tablename}' AND b.TABLE_NAME = a.TABLE_NAME AND b.COLUMN_NAME = a.COLUMN_NAME".format(tablename=tablename.upper()))
            tableresult = cursor.fetchall()
            datadict = []
            for i in tableresult:
                datadict.append({
                    "columns": i[0],
                    "columntype": i[1],
                    "desc": i[2],
                })
            data['data'] = datadict[(page - 1) * limit:limit + (page - 1) * limit]
            data['code'] = 0
            data['count'] = len(tableresult)

    return JsonResponse(data)

def searchindex(request):
    '''渲染数据资源检索页面'''

    userid = request.session.get('user_id')
    if not userid:
        userid = ''
    data = {'userid':userid}
    return render(request,"search_index.html",data)


def get_resource(request):
    '''
    资源搜索主页,获取所有资源
    :param request:
    :return:
    '''
    response_data={}
    current_page=request.GET.get('page') # 当前页码
    limit = request.GET.get('limit')  # 限制条数
    kw = request.GET.get('kw')
    id = request.GET.get('id')
    if kw and id:
        resource_obj = []
        sub_node = Mulu.objects.filter(parent_id=id)
        #  如果有子目录获取子级目录下的信息
        if sub_node:
            for sub in sub_node:
                # 再次获取子级目录下是否有子级目录
                sub2_node = Mulu.objects.filter(parent_id=sub.id)
                if sub2_node:
                    for sub2 in sub2_node:
                        resource_obj += TdsdResourceList.objects.filter(resource_cate=sub2.id).filter(
                            status=0).filter(Q(resource_name__contains=kw)|Q(resource_desc__contains=kw)|Q(resource_content__contains=kw)).order_by("-updatetime")
                else:
                    resource_obj += TdsdResourceList.objects.filter(resource_cate=sub.id).filter(status=0).filter(Q(resource_name__contains=kw)|Q(resource_desc__contains=kw)|Q(resource_content__contains=kw)).order_by(
                        "-updatetime")
        else:
            resource_obj = TdsdResourceList.objects.filter(status=0).filter(resource_cate=id).filter(Q(resource_name__contains=kw)|Q(resource_desc__contains=kw)|Q(resource_content__contains=kw)).order_by("-updatetime")
    elif kw:
        # 根据资源名、摘要、内容搜索数据。
        resource_obj=TdsdResourceList.objects.filter(status=0).filter(Q(resource_name__contains=kw)|Q(resource_desc__contains=kw)|Q(resource_content__contains=kw)).order_by("-updatetime")
    elif id:
        resource_obj = []
        sub_node = Mulu.objects.filter(parent_id=id)
        #  如果有子目录获取子级目录下的信息
        if sub_node:
            for sub in sub_node:
                # 再次获取子级目录下是否有子级目录
                sub2_node = Mulu.objects.filter(parent_id=sub.id)
                if sub2_node:
                    for sub2 in sub2_node:
                        resource_obj += TdsdResourceList.objects.filter(resource_cate=sub2.id).filter(status=0).order_by("-updatetime")
                else:
                    resource_obj += TdsdResourceList.objects.filter(resource_cate=sub.id).filter(status=0).order_by("-updatetime")
        else:
            resource_obj = TdsdResourceList.objects.filter(status=0).filter(resource_cate=id).order_by("-updatetime")
    else:
        resource_obj = TdsdResourceList.objects.filter(status=0).all().order_by("-updatetime")
    count_data = len(resource_obj)
    if resource_obj:
        # 创建分页对象
        paginator = Paginator(resource_obj,limit)
        # 当前页的数据，返回
        page = paginator.page(current_page)
        for i in page.object_list:
            user_obj = TdsdUser.objects.filter(id=i.userid, isdelete=0).first()
            root_dir_id = user_obj.dir_id if user_obj else ""
            muluobj=Mulu.objects.filter(id=root_dir_id, isdelete=0).first() if root_dir_id else  ""
            if muluobj:
                root_name = muluobj.name
            else:
                root_name=""
            response_data.setdefault('data',[]).append({
                "resource_id":i.resource_id,
                "resource_name":i.resource_name,
                "resource_cate":i.resource_cate,
                "resource_desc":i.resource_desc,
                "resource_content":i.resource_content,
                "resource_status":"需授权" if i.resource_status==0 else "公开",#
                "addtimes":time.strftime("%Y-%m-%d %H:%M:%S",time.localtime(i.updatetime)),#
                "userid":root_name,# 发布部门
                "db_id":i.db_id,
            })
        response_data['code'] = 0
        response_data['count'] = count_data
        return JsonResponse(response_data)
    else:
        response_data['code'] = 1
        return JsonResponse(response_data)


def resourcedesc(request,resource_id):
    '''
    资源详情页
    :param request:
    :param resource_id:
    :return:
    '''
    resource_id=int(resource_id)
    userid = request.session.get('user_id')
    apply_for_status=""
    column_list=[]
    if userid:
        apply_for_record=TdsdApplyResource.objects.values("status","token","id").filter(apply_user_id=userid).filter(resource_id=resource_id).order_by('-apply_time')

        print(apply_for_record)
        if apply_for_record:
            apply_for_status = apply_for_record.first().get('status')
            token = apply_for_record.first().get("token")
        else:
            pass

        if apply_for_status==2:
            print(userid,resource_id)
            # refuse_message=TdsdMessage.objects.values('message').get(sender_id=userid,apply_resource_tb_id=resource_id).get('message')
            apply_resource=TdsdApplyResource.objects.filter(apply_user_id=userid, resource_id=resource_id, isdelete=0).order_by('-apply_time').first()
            if apply_resource:
                apply_resource_tb_id = apply_resource.id
                print("?>>>>>>>>>=",apply_resource_tb_id,userid)
                refuse_message = TdsdMessage.objects.values('message').filter(apply_resource_tb_id=apply_resource_tb_id).filter(user_id=userid).filter(isdelete=0).order_by("-push_time").first().get("message")
            else:
                refuse_message = '未查到相关信息'  # TODO
    result={}
    tabledata_10,tabledata_10_last=[],[]
    try:
        sql="select a.`userid`,a.`tablename`,a.`db_id`,a.`resource_id`,a.`resource_name`,a.`resource_desc`,a.`resource_content`,\
                                          addtimes,updatetime,a.`resource_info`,b.`name`,c.`username` \
                                          from tdsd_resource_list as a inner join tdsd_mulu as b INNER JOIN tdsd_user as c \
                                            where a.resource_cate=b.id and a.userid=c.id and a.resource_id={a} and a.status=0".format(a=resource_id)
        data=TdsdResourceList.objects.raw(sql)
        print("data=",data.raw_query)
        for i in data:
            db_id = i.db_id
            resource_user_id=i.userid
            username=i.username
            openStatus=i.resource_status
            belongself= resource_user_id==request.session.get('user_id') #当前资源是否属于用户自己
            print("openStatus=",openStatus,"belongself",belongself)
            result['resource_name']=i.resource_name
            result['resource_desc']=i.resource_desc
            result['addtimes']=time.strftime("%Y-%m-%d %H:%M:%S",time.localtime(i.addtimes))
            result['updatetime']=time.strftime("%Y-%m-%d %H:%M:%S",time.localtime(i.updatetime))
            result['resource_cate']=i.name
            result['resource_status']="需授权" if i.resource_status==0 else "公开"
            result['resource_content']=i.resource_content
            result['username']=i.username
            result['userid']=i.userid
            columns = json.loads(i.resource_info)
            print("columns=",columns)
            dbinfo = TdsdDataSource.objects.filter(id=db_id).first()
            columnsdesc=columns
            columsstr = ""
            for n in columns:
                column_list.append(n.get('columns'))
                if dbinfo.db_cate=="MySQL":
                    columsstr += '`{col}`'.format(col=n.get('columns')) + ','
                elif dbinfo.db_cate=="Oracle":
                    columsstr += n.get('columns') + ','
            cloumnslist = columsstr[:-1].split(',')

            if dbinfo.db_cate=="MySQL":
                print("xxx")
                conn_url_dict = {
                    "host": dbinfo.host_ip,
                    "user": dbinfo.username,
                    "password": dbinfo.password,
                    "db": dbinfo.sid,
                    "charset": "utf8",
                    "cursorclass": pymysql.cursors.DictCursor

                }

                conn = pymysql.connect(**conn_url_dict)
                with conn.cursor() as cursor:
                    sql="select {columns} from {tablename} limit 0,10".format(
                        tablename=i.tablename,columns=columsstr[:-1]
                    )
                    print(sql)
                    cursor.execute(sql)
                    tabledata_10 = cursor.fetchall()
            elif dbinfo.db_cate=="Oracle":
                print("oracle")
                oracleUrl = '{user}/{passwd}@{ip}/{db}'.format(user=dbinfo.username, passwd=dbinfo.password,
                                                               ip=dbinfo.host_ip, db=dbinfo.sid)
                conn = cx_Oracle.connect(oracleUrl)
                with conn.cursor() as cursor:
                    sql="select {columns} from {tablename} where rownum BETWEEN 0 and 10".format(
                        tablename=i.tablename, columns=columsstr[:-1]
                    )
                    print(sql)
                    cursor.execute(sql)
                    tabledata_10 = cursor.fetchall()
            break
        else:
            return render_to_response('404.html', locals())

    except Exception as e:
        traceback.print_exc()
    firstExample=[]
    if tabledata_10:
        if isinstance(tabledata_10[0],dict):
            for v in tabledata_10:
                tabledata_10_last.append(v.values())
            firstExample=tabledata_10[0].values() #example
        else:
            tabledata_10_last=tabledata_10
            firstExample=tabledata_10[0]
    _example=str(dict(zip(column_list,firstExample))).replace(',',',\n')
    example_data='''
{
  "msg": "",
  "count": 1,
  "code": 0,
  "data": [
    %s
  ]
}
    '''%(str(_example))
    return render_to_response('resource_desc.html',locals())

def resourceinfo(request,resource_id):
    '''
    获取资源详情的api
    :param request:
    :param resource_id:
    :return:
    '''
    data={}
    result=TdsdResourceList.objects.filter(resource_id=resource_id).first()
    if result:
        data.setdefault('tableanddb',{})['table']=result.tablename
        data.setdefault('tableanddb',{})['db']=result.db_id
        data.setdefault('detail',{})['resource_cate']=result.resource_cate
        data.setdefault('detail',{})['resource_desc']=result.resource_desc
        data.setdefault('detail',{})['resource_content']=result.resource_content
        data.setdefault('detail',{})['resource_status']=result.resource_status
        data.setdefault('detail',{})['resource_name']=result.resource_name
        data.setdefault('detail',{})['expiretime']=result.expiretime
        data.setdefault('detail',{})['blackbook']=result.blackbook
        columns=json.loads(result.resource_info)
        data['data']=columns
        cateresult=Mulu.objects.filter(id=result.resource_cate).first()
        twocate=cateresult.parent_id
        if twocate:
            tworesult=Mulu.objects.filter(id=twocate).first()
            onecate=tworesult.parent_id
            if onecate:
                data.setdefault('cateid',{})["three"]=result.resource_cate
                data.setdefault('cateid',{})["two"]=twocate
                data.setdefault('cateid',{})["one"]=onecate
                data.setdefault('cateid',{})['threename']=Mulu.objects.values('name').get(id=result.resource_cate).get('name')
            else:
                data.setdefault('cateid',{})["three"]=""
                data.setdefault('cateid',{})["two"]=result.resource_cate
                data.setdefault('cateid',{})["one"]=twocate



    data['code']=0
    data['msg']=''
    print(data)
    return JsonResponse(data)

def ftpresourceinfo(request,resource_id):
    '''

    :param request:
    :param resource_id:
    :return:
    '''
    data={}
    info=TdsdFtpResourceList.objects.get(id=resource_id)
    data['code']=0
    data['data']={
        "ftpname":info.fname,
        "ftpaddress":info.ftpid,
        "desc":info.desc,
        "resource_status":info.resource_status,
        "filename":info.filename,
        "fid":info.id,
        "upload_file_url":info.fileurl
    }
    data['msg']=""
    return JsonResponse(data)

@util.api_verification
def resourceapihandle(request,resource_id):
    '''
    获取资源的api函数
    :param request:
    :param resource_id:
    :return:
    '''
    data={}
    column_list,tabledata_10=[],[]
    page=int(request.GET.get('page',1))
    limit=1000
    resource_info=TdsdResourceList.objects.get(resource_id=resource_id)
    if resource_info:
        try:
            dbinfo=TdsdDataSource.objects.get(id=resource_info.db_id)
            columns = json.loads(resource_info.resource_info)
            print(columns)
            columsstr = ""
            start = int(page - 1) * limit
            end=start+limit
            tablename = resource_info.tablename
            for n in columns:
                column_list.append(n.get('columns'))
                if dbinfo.db_cate == "MySQL":
                    columsstr += '`{col}`'.format(col=n.get('columns')) + ','
                elif dbinfo.db_cate == "Oracle":
                    columsstr += n.get('columns') + ','
            columsstr = columsstr[:-1]
            if dbinfo.db_cate=="MySQL":
                conn_url_dict = {
                    "host": dbinfo.host_ip,
                    "user": dbinfo.username,
                    "password": dbinfo.password,
                    "db": dbinfo.sid,
                    "charset": "utf8",
                    "cursorclass": pymysql.cursors.DictCursor

                }
                print(conn_url_dict)
                conn = pymysql.connect(**conn_url_dict)
                with conn.cursor() as cursor:
                    sql=f"select {columsstr} from {tablename} limit {start},1000"
                    print(sql)
                    cursor.execute(sql)
                    tabledata_10 = cursor.fetchall()

            elif dbinfo.db_cate=="Oracle":
                oracleUrl = '{user}/{passwd}@{ip}/{db}'.format(user=dbinfo.username, passwd=dbinfo.password,
                                                               ip=dbinfo.host_ip, db=dbinfo.sid)
                conn = cx_Oracle.connect(oracleUrl)
                with conn.cursor() as cursor:
                    # sql = f"select {columsstr} from {tablename} where rownum BETWEEN  {start} and {end}"
                    sql=f'select {columsstr} from (select {columsstr},rownum rn from {tablename}) a1 where rn between {start} and {end}'
                    cursor.execute(sql)
                    tabledata_10 = cursor.fetchall()
                    tabledata_10=[dict(zip(column_list,i)) for i in tabledata_10]
        except Exception as e:
            data['code']=2
            data['msg']=str(e)
            data['data']=[]
        # 加入api使用记录
        try:
            util.record_api_use(request,resource_info.userid,resource_info.resource_status)
        except Exception as e:
            data['msg']='api使用记录插入错误'
        else:
            data['code']=0
            data['msg']=""
            data['count']=len(tabledata_10)
            data['data']=tabledata_10
    else:
        data['code']=1
        data['msg']='没有找到记录'

    return JsonResponse(data)


def resourcedownload(request,resource_id):

    column_list,tabledata_10=[],[]
    page=int(request.GET.get('page',1))
    limit=1000
    resource_info=TdsdResourceList.objects.get(resource_id=resource_id)
    filename = "{timestep}data.csv".format(timestep=time.strftime("%Y%m%d%H%M%S", time.localtime(time.time())))
    if resource_info:
        dbinfo=TdsdDataSource.objects.get(id=resource_info.db_id)
        columns = json.loads(resource_info.resource_info)
        columsstr = ""
        start = int(page - 1) * limit
        end=start+limit
        tablename = resource_info.tablename
        for n in columns:
            column_list.append(n.get('columns'))
            if dbinfo.db_cate == "MySQL":
                columsstr += '`{col}`'.format(col=n.get('columns')) + ','
            elif dbinfo.db_cate == "Oracle":
                columsstr += n.get('columns') + ','
        columsstr = columsstr[:-1]
        resource_data=util.getDbinfoByDbId(dbinfo,columsstr,tablename,start,end)
        with open(filename, 'w', encoding='utf-8') as csvfile:
            spamwriter = csv.writer(csvfile, dialect='excel')
            spamwriter.writerow(column_list)
            for i in resource_data:
                item=i.values()
                spamwriter.writerow(item)

    def file_iterator(file_name, chunk_size=512):  # 用于形成二进制数据
        with open(file_name, 'rb') as f:
            while True:
                c = f.read(chunk_size)
                if c:
                    yield c
                else:
                    break
        try:
            os.system("rm -rf {store_file_name}".format(store_file_name=filename))
        except Exception as e:
            print(str(e))
    the_file_name = filename  # 要下载的文件路径
    response = StreamingHttpResponse(file_iterator(the_file_name))  # 这里创建返回
    response['Content-Type'] = 'application/vnd.ms-excel'  # 注意格式
    response['Content-Disposition'] = 'attachment;filename="{filename}"'.format(filename=filename)  # 注意filename 这个是下载后的名字
    return response


def ftpresourcedownload(request,resource_id):
    '''
    ftp文件下载
    :param request:
    :param resource_id:
    :return:
    '''

    resource_info=TdsdFtpResourceList.objects.get(id=resource_id)
    filename = resource_info.filename
    # store_file_name=STORE_DIR_FTP+filename #本地保存的文件路径
    if not os.path.exists(filename):
        if resource_info:
            ftpinfo=TdsdFtpSource.objects.get(ftp_id=resource_info.ftpid)
            try:
                f = ftplib.FTP(ftpinfo.ftp_ip)
                f.login(ftpinfo.ftp_username,ftpinfo.ftp_password)
                f.encoding='utf-8'
                bufsize=1024
                fp=open(filename,'wb')
                f.retrbinary('RETR %s' % filename, fp.write, bufsize)
                fp.close()
                f.close()
            except Exception as e:
                traceback.print_exc()
    def file_iterator(file_name, chunk_size=512):  # 用于形成二进制数据
        with open(file_name, 'rb') as f:
            while True:
                c = f.read(chunk_size)
                if c:
                    yield c
                else:
                    break
        try:
            os.system("rm -rf {store_file_name}".format(store_file_name=filename))
        except Exception as e:
            print(str(e))
    response = StreamingHttpResponse(file_iterator(filename))  # 这里创建返回
    response['Content-Type'] = 'application/octet-stream'  # 注意格式
    response['Content-Disposition'] = 'attachment;filename={filename}'.format(filename=escape_uri_path(filename))  # 注意filename 这个是下载后的名字
    return response

def appkey(request):
    '''

    :param request:
    :return:
    '''
    data={}
    try:
        userid = request.session.get('user_id')
        appkey=TdsdUser.objects.values("appkey").get(id=userid).get("appkey")
        assert userid,u"用户不能为空"
        assert  appkey,u"appkey未找到"
    except Exception as e:
        data['msg']=str(e)
        data['code']=1
    else:
        data['data']=appkey
        data['code']=0
    return JsonResponse(data)

@csrf_exempt
def uploadfile(request):
    '''
    上传ftp文件
    :param request:
    :return:
    '''
    data={}
    print(request.FILES)
    file=request.FILES.get("file")
    filename=file.name
    params=request.POST
    ftp_id=params.get("chc_ftp_address")
    try:
        assert ftp_id,"FTP服务器不能为空"
        ftpinfo=TdsdFtpSource.objects.get(
            ftp_id=ftp_id
        )
        upload_status,error,fileurl=util.ftp_upload(ftpinfo.ftp_username,ftpinfo.ftp_password,ftpinfo.ftp_ip,file,filename)
        if uploadfile:
            data['code']=0
            data['data']=fileurl
        else:
            data['code']=1
        data['msg']=""
    except Exception as e:
        data['code']=1
        data['msg']=str(e)
    print("data=",data)
    return JsonResponse(data)

def testupload(request):
    return render_to_response('upload.html',locals())

@csrf_exempt
def fileupload(request):
    if request.method == 'POST':
        upload_file = request.FILES.get('file')
        task = request.POST.get('task_id')  # 获取文件唯一标识符
        chunk = request.POST.get('chunk', 0)  # 获取该分片在所有分片中的序号
        filename = '%s%s' % (task, chunk)  # 构成该分片唯一标识符
        print("filename=",filename)
        default_storage.save('./upload/%s' % filename,ContentFile(upload_file.read()))  # 保存分片到本地
    return render_to_response('upload.html',locals())

def testone(request):
    return render_to_response('test.html',locals())

@csrf_exempt
def fileMerge(request):
    print(request.GET)
    task = request.GET.get('task_id')
    ext = request.GET.get('filename', '')
    upload_type = request.GET.get('type')
    if len(ext) == 0 and upload_type:
        ext = upload_type.split('/')[1]
    ext = '' if len(ext) == 0 else '.%s' % ext  # 构建文件后缀名
    chunk = 0
    with open('./upload/%s%s' % (task, ext), 'wb') as target_file:  # 创建新文件
        while True:
            try:
                filename = './upload/%s%d' % (task, chunk)
                source_file = open(filename, 'rb')  # 按序打开每个分片
                target_file.write(source_file.read())  # 读取分片内容写入新文件
                source_file.close()
            except IOError:
                break
            chunk += 1
            os.remove(filename)  # 删除该分片，节约空间
    return render_to_response('upload.html',locals())



def page_not_found(request):
    response = render_to_response('404.html',{})
    response.status_code=404
    return response


def searchftp(request):
    userid = request.session.get('user_id')
    if not userid:
        userid = ''
    data = {'userid': userid}
    return render(request, "search_ftpresouce.html", data)


def search_ftpresouce(request):
    """
    ftp上传文件资源展示
    :param request:
    :return:
    """
    response_data = {}
    current_page = request.GET.get('page')  # 当前页码
    limit = request.GET.get('limit')  # 限制条数
    kw = request.GET.get('kw')
    id = request.GET.get('id')
    if kw and id: #目录及搜索内容同时筛选
        ftpresource_obj = []
        sub_node = Mulu.objects.filter(parent_id=id)
        #  如果有子目录获取子级目录下的信息
        if sub_node:
            for sub in sub_node:
                # 再次获取子级目录下是否有子级目录
                sub2_node = Mulu.objects.filter(parent_id=sub.id)
                if sub2_node:
                    for sub2 in sub2_node:
                        ftpresource_obj += TdsdFtpResourceList.objects.filter(resource_cate=sub2.id).filter(
                            isdelete=0).filter(Q(filename__contains=kw) | Q(fname__contains=kw) | Q(fid__contains=kw) | Q(desc__contains=kw)).order_by("-updatetimes")
                else:
                    ftpresource_obj += TdsdFtpResourceList.objects.filter(resource_cate=sub.id).filter(isdelete=0).filter(
                        Q(filename__contains=kw) | Q(fname__contains=kw) | Q(fid__contains=kw) | Q(desc__contains=kw)).order_by("-updatetimes")
        else:
            ftpresource_obj = TdsdFtpResourceList.objects.filter(isdelete=0).filter(resource_cate=id).filter(
                Q(filename__contains=kw) | Q(fname__contains=kw) | Q(fid__contains=kw) | Q(desc__contains=kw)).order_by("-updatetimes")
    elif kw:
        # 根据ftp上传资源名、文件名、资源描述、文件唯一识别符搜索数据。
        ftpresource_obj = TdsdFtpResourceList.objects.filter(isdelete=0).filter(
            Q(filename__contains=kw) | Q(fname__contains=kw) | Q(fid__contains=kw) | Q(desc__contains=kw)).order_by("-updatetimes")
    elif id:
        ftpresource_obj = []
        sub_node = Mulu.objects.filter(parent_id=id)
        #  如果有子目录获取子级目录下的信息
        if sub_node:
            for sub in sub_node:
                # 再次获取子级目录下是否有子级目录
                sub2_node = Mulu.objects.filter(parent_id=sub.id)
                if sub2_node:
                    for sub2 in sub2_node:
                        ftpresource_obj += TdsdFtpResourceList.objects.filter(resource_cate=sub2.id).filter(
                            isdelete=0).order_by("-updatetimes")
                else:
                    ftpresource_obj += TdsdFtpResourceList.objects.filter(resource_cate=sub.id).filter(isdelete=0).order_by(
                        "-updatetimes")
        else:
            ftpresource_obj = TdsdFtpResourceList.objects.filter(isdelete=0).filter(resource_cate=id).order_by("-updatetimes")
    else:
        ftpresource_obj = TdsdFtpResourceList.objects.filter(isdelete=0).all().order_by("-updatetimes")
    count_data = len(ftpresource_obj)
    if ftpresource_obj:
        # 创建分页对象
        paginator = Paginator(ftpresource_obj, limit)
        # 当前页的数据，返回
        page = paginator.page(current_page)
        for i in page.object_list:
            user_obj = TdsdUser.objects.filter(id=i.uid, isdelete=0).first()
            user_name = user_obj.username if user_obj else "" # 文件上传人
            root_dir_id = user_obj.dir_id if user_obj else ""
            muluobj = Mulu.objects.filter(id=root_dir_id, isdelete=0).first() if root_dir_id else  ""
            if muluobj:
                root_name = muluobj.name #文件上传部门
            else:
                root_name = ""
            response_data.setdefault('data', []).append({
                "id": i.id,
                "fid": i.fid, # 数据唯一标识符
                "fname": i.fname, # 资源名称
                "addtimes":time.strftime("%Y-%m-%d %H:%M:%S",time.localtime(i.updatetimes)) if i.updatetimes else time.strftime("%Y-%m-%d %H:%M:%S",time.localtime(i.addtimes)), # 上传时间
                "uid": user_name, # 用户名
                "resouce_cate": root_name,  # 发布部门
                "desc": i.desc, # 资源描述
                "resource_status":"公开" if i.resource_status else "需授权",
                "filename":i.filename,
                "filurl":i.fileurl, # 下载地址
            })
        response_data['code'] = 0
        response_data['count'] = count_data
        return JsonResponse(response_data)
    else:
        response_data['code'] = 1
        return JsonResponse(response_data)